import pyodbc
import bs4
import re
this_number = 3
if this_number == 0:
#~~~~~~Browser_and_Sql_Parameters~~~~~~#
    sql_driver = '{ODBC driver 17 for SQL Server}'
    server = 'Yu-GODS-LAPTOP'
    database= 'Project'
    trusted_connection = 'yes'
    uid = ''
    pwd = ''
    table = 'Project.dbo.ET_InvestNews'
    table_tag = 'Project.dbo.ET_InvestTags'
    table_relation_0 = 'Project.dbo.ET_InvestNewsToEnterprise'
    table_relation_1 = 'Project.dbo.ET_InvestRelationEnterprise'
    table_img = 'Project.dbo.ET_InvestImg'
    port = ""
    main_path = r"C:\Users\Yu\Documents\BullShit\Python\selenium\extract_text\text_extract"+ "\\"
#~~~~~~Browser_and_Sql_Parameters~~~~~~#~~~~~~65~~~~~~#
elif this_number == 1:
    sql_driver = '{ODBC driver 17 for SQL Server}'
    server = 'ECS-2566' 
    database = 'ProjectManager'
    trusted_connection = 'yes'
    uid = 'teda2020'  
    pwd = 'App1234.ww'
    table = 'ProjectManager.dbo.ET_InvestNews'
    table_tag = 'ProjectManager.dbo.ET_InvestTags'
    table_relation_0 = 'ProjectManager.dbo.ET_InvestNewsToEnterprise'
    table_relation_1 = 'ProjectManager.dbo.ET_InvestRelationEnterprise'
    table_img = 'ProjectManager.dbo.ET_InvestImg'
    port = ""
    main_path = r"C:\Users\Administrator\Desktop\InvestNewsTrail\extract_text\extract_text" + "\\"
#~~~~~~Browser_and_Sql_Parameters~~~~~~#~~~~~~81~~~~~~#
elif this_number == 2:
    sql_driver = '{ODBC driver 17 for SQL Server}'
    server = '192.168.85.51,1789' 
    database = 'Test0620'
    trusted_connection = 'no'
    uid = 'sa'  
    pwd = 'App1234'
    port = '1433'
    table = 'Test0620.dbo.ET_InvestNews'
    table_tag = 'Test0620.dbo.ET_InvestTags'
    table_relation_0 = 'Test0620.dbo.ET_InvestNewsToEnterprise'
    table_relation_1 = 'Test0620.dbo.ET_InvestRelationEnterprise'
    table_img = 'Test0620.dbo.ET_InvestImg'
    main_path = r"F:\InvestNews\extract_text\extract_text" + "\\"
elif this_number == 3:
    sql_driver = '{ODBC driver 17 for SQL Server}'
    server = '192.168.85.51,1789' 
    database = 'ET_dbForEN'
    trusted_connection = 'no'
    uid = 'sa'  
    pwd = 'App1234'
    port = '1433'
    table = 'ET_dbForEN.dbo.ET_InvestNews'
    table_tag = 'ET_dbForEN.dbo.ET_InvestTags'
    table_relation_0 = 'ET_dbForEN.dbo.ET_InvestNewsToEnterprise'
    table_relation_1 = 'ET_dbForEN.dbo.ET_InvestRelationEnterprise'
    table_img = 'ET_dbForEN.dbo.ET_InvestImg'
    main_path = r"F:\extract_text\extract_text" + "\\"

connect_input = "driver=" + sql_driver + ";"
connect_input = connect_input + "Server=" + server + ";"
connect_input = connect_input + "Database=" + database + ";"
connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
if port != "":
    connect_input = connect_input + "PORT=" + port + ";"
if uid != '' and pwd != '':
    connect_input = connect_input + "UID=" + uid + ";"
    connect_input = connect_input + "PWD=" + pwd
try:
    conn = pyodbc.connect(connect_input)
except Exception as e:
    print(e)
    input()
cursor = conn.cursor()
#~~~~~~Make_Existing_Title_List_Filter~~~~~~#
order_0 = "select ID,Title,Contents,CreateDate from " + table + " where Status = 1 order by CreateDate DESC"
cursor.execute(order_0)
article_list = list(cursor.fetchall())

i = 0
for article in article_list:    
    article_text = bs4.BeautifulSoup(str(article[2]), 'html.parser').text
    key_list = ['股票', 'A股', '上涨', '下跌', ]
    del_para = 0
    for key in key_list:
        if key in article_text:
            del_para = 1
            break
    if del_para == 1:
        continue
    else:
        pass
    title = article[1]
    ID = article[0]
    create_date = article[3]
    spc_char_list = re.findall("\W", title)
    for spc_char in spc_char_list:
        title = title.replace(spc_char, "_")
    file_name = title + '.txt'
    file_path = main_path + title + '.txt'
    with open(file_path, 'w+', encoding='utf-8') as f:
        f.write(ID + '\n')
        f.write(str(create_date) + '\n')
        f.write(article_text)
        f.close()
    print(file_name, i)
    i += 1
    if i > 3000:
        break